package fy.bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import fy.util.DBUtil;
             //类名
public class NewsBean {
	//属性（成员变量）
	private int id;//私有
	private String category;
	private String title;
	private String content;
	private int homepage;
	private String author;
	private Date time;
	private String picturetittle;
	private int picturepostion;
	private String source;
	private String link;
	private String picture;
	private int puton;
	private String categoryname;
	
	//protected 本包
	PreparedStatement statement=null;//准备/操作
	ResultSet rs = null;//结果集
	Connection connection = null;//连接
	
	public String getCategoryname() {
		return categoryname;
	}
	public void setCategoryname(String categoryname) {
		this.categoryname = categoryname;
	}
	
	public int getPuton() {
		return puton;
	}
	public void setPuton(int puton) {
		this.puton = puton;
	}
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	
	public String getCategory() {
		return category;
	}
	public void setCategory(String category) {
		this.category = category;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String tittle) {
		this.title = tittle;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public int getHomepage() {
		return homepage;
	}
	public void setHomepage(int homepage) {
		this.homepage = homepage;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public Date getTime() {
		return time;
	}
	public void setTime(Date time) {
		this.time = time;
	}
	public String getPicturetittle() {
		return picturetittle;
	}
	public void setPicturetittle(String picturetittle) {
		this.picturetittle = picturetittle;
	}
	public int getPicturepostion() {
		return picturepostion;
	}
	public void setPicturepostion(int picturepostion) {
		this.picturepostion = picturepostion;
	}
	public String getSource() {
		return source;
	}
	public void setSource(String source) {
		this.source = source;
	}
	public String getLink() {
		return link;
	}
	public void setLink(String link) {
		this.link = link;
	}
	public String getPicture () {
		return picture; 
	}
	public void setPicture(String picture) {
		this.picture = picture;
	}
//放在首页的方法
	//
	public List<NewsBean> findOnindex() {
		List<NewsBean> list = new ArrayList<NewsBean>();
		DBUtil dbUtil = new DBUtil();
		String sql = "select * from news where homepage=1 and puton=1 order by time desc ";
		connection = dbUtil.geConnection();
		try {
			statement = connection.prepareStatement(sql);
			rs = statement.executeQuery();
			while(rs.next()) {
				NewsBean newBean = new NewsBean();
				newBean.setId(rs.getInt("id"));
				newBean.setTitle(rs.getString("title"));
				newBean.setContent(rs.getString("content"));
				newBean.setAuthor(rs.getString("author"));
				newBean.setSource(rs.getString("source"));
				newBean.setTime(rs.getDate("time"));
				newBean.setPicture(rs.getString("picture"));
				list.add(newBean);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return list;
		
	}
	
	public NewsBean findOnindexbyId(int id) {
		NewsBean newBean = new NewsBean();
		DBUtil dbUtil = new DBUtil();
		String sql = "select * from news where id=? and homepage=1 and puton=1 order by time desc ";
		connection = dbUtil.geConnection();
		try {
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			rs = statement.executeQuery();
			while(rs.next()) {
				newBean.setId(rs.getInt("id"));
				newBean.setTitle(rs.getString("title"));
				newBean.setContent(rs.getString("content"));
				newBean.setAuthor(rs.getString("author"));
				newBean.setSource(rs.getString("source"));
				newBean.setTime(rs.getDate("time"));
				newBean.setPicture(rs.getString("picture"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return newBean;
		
	}
	
	public  List<NewsBean> findOnindexbylb(String lbString) {
		List<NewsBean> list = new ArrayList<NewsBean>();
		
		DBUtil dbUtil = new DBUtil();
		String sql = "select * from news where category=? and homepage=1 and puton=1 order by time desc ";
		
		connection = dbUtil.geConnection();
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, lbString);
			System.out.println(statement.toString());
			rs = statement.executeQuery();
			while(rs.next()) {
				NewsBean newBean = new NewsBean();
				newBean.setId(rs.getInt("id"));
				newBean.setTitle(rs.getString("title"));
				newBean.setContent(rs.getString("content"));
				newBean.setAuthor(rs.getString("author"));
				newBean.setSource(rs.getString("source"));
				newBean.setTime(rs.getDate("time"));
				newBean.setPicture(rs.getString("picture"));
				list.add(newBean);
				System.out.println(rs.getString("title"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return list;
		
	}
public NewsBean findPreOnindex(int id) {
		System.out.println("run findNewsByid  nad ib");
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		NewsBean newBean = new NewsBean();
		String sql = "select * from news where id < ? and homepage=1 and puton=1 order by id desc limit 1 ";
		try {
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			System.out.println(sql);
			rs = statement.executeQuery();
			System.out.println(sql);
			
			if(rs.next()) {
				newBean.setId(rs.getInt("id"));
				newBean.setTitle(rs.getString("title"));
				newBean.setContent(rs.getString("content"));
				newBean.setTime(rs.getDate("time"));
				newBean.setAuthor(rs.getString("author"));
				newBean.setSource(rs.getString("source"));
				newBean.setPicture(rs.getString("picture"));
				newBean.setPuton(rs.getInt("puton"));
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}

		return newBean;
		
	}
	public NewsBean findNextOnindex(int id) {
		System.out.println("run findNewsByid  nad ib");
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		NewsBean newBean = new NewsBean();
		String sql = "select * from news where id > ? and homepage=1 and puton=1 order by id asc limit 1 ";
		try {
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			System.out.println(sql);
			rs = statement.executeQuery();
			System.out.println(sql);
			
			if(rs.next()) {
				newBean.setId(rs.getInt("id"));
				newBean.setTitle(rs.getString("title"));
				newBean.setContent(rs.getString("content"));
				newBean.setTime(rs.getDate("time"));
				newBean.setAuthor(rs.getString("author"));
				newBean.setSource(rs.getString("source"));
				newBean.setPicture(rs.getString("picture"));
				newBean.setPuton(rs.getInt("puton"));
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}

		return newBean;
		
	}

	
	//查询全部内容
	public List<NewsBean> findAll() {
		List<NewsBean> list = new ArrayList<NewsBean>();
		DBUtil dbUtil = new DBUtil();
		String sql = "select * from news where puton=1";
		connection = dbUtil.geConnection();
		try {
			statement = connection.prepareStatement(sql);
			rs = statement.executeQuery();
			while(rs.next()) {
				NewsBean newBean = new NewsBean();
				newBean.setId(rs.getInt("id"));
				newBean.setTitle(rs.getString("title"));
				//newBean.setContent(rs.getString("content"));
				//newBean.setAuthor(rs.getString("author"));
				newBean.setSource(rs.getString("source"));
				newBean.setTime(rs.getDate("time"));
				newBean.setPicture(rs.getString("picture"));
			    newBean.setPuton(rs.getInt("puton"));
				list.add(newBean);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return list;
		
	}
	
   	
	public boolean doAdd(String category,String title,String content,Integer homepage,String picture,String author,String source,String txtDate,int puton,String categoryname) {
		boolean add = false;
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		//String sql = "select password from admin where username = " + username + "'";
		String sql = "insert into news(category,title,content,homepage,picture,author,source,time,puton,categoryname) values(?,?,?,?,?,?,?,?,?,?)";
		
	  try {		
		  PreparedStatement statement = connection.prepareStatement(sql);
			statement.setString(1, category);
			statement.setString(2, title);
			statement.setString(3, content);
			statement.setInt(4, homepage);
			statement.setString(5, picture);
			statement.setString(6, author);
			statement.setString(7, source);
			statement.setString(8, txtDate);
			statement.setInt(9, puton);
			statement.setString(10, categoryname);
			System.out.println(add);
			int i = statement.executeUpdate();
			if (i>0) {
				add = true;
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return add;
		
	}
	
	public boolean doDel(int id) {
		boolean del = false;
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		//String sql = "select password from admin where username = " + username + "'";
		String sql = "delete from news where id =?";
		try {
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			int i = statement.executeUpdate();
			if (i>0) {
				del = true;
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return del;
		
	}
	public boolean doUpdata(String category,String title,String content,Integer homepage,String picture,String author,String source,String txtDate,int puton,String categoryname,int id) {
		boolean updata = false;
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		//String sql = "select password from admin where username = " + username + "'";
		String sql = "update news set category =?,title =?,content =?,homepage =?,picture =?,author =?,source=?,time=?,puton=?,categoryname=? where id =?";
		try {
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setString(1, category);
			statement.setString(2, title);
			statement.setString(3, content);
			statement.setInt(4, homepage);
			statement.setString(5, picture);
			statement.setString(6, author);
			statement.setString(7, source);
			statement.setString(8, txtDate);
			statement.setInt(9, puton);
			statement.setString(10, categoryname);
			statement.setInt(11, id);
			System.out.println(updata);
			int i = statement.executeUpdate();
			if (i>0) {
				updata = true;
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return updata;
		
	}
	

	//通过id查找内容
public NewsBean findNewsById(int id) {
		System.out.println("run findNewsById : " + id);
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		NewsBean newBean = new NewsBean();
		String sql = "select * from news where id = ?";
		try {
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			rs = statement.executeQuery();
			if(rs.next()) {
				newBean.setTitle(rs.getString("title"));
				newBean.setContent(rs.getString("content"));
				newBean.setTime(rs.getDate("time"));
				newBean.setAuthor(rs.getString("author"));
				newBean.setSource(rs.getString("source"));
				newBean.setPicture(rs.getString("picture"));
				newBean.setCategory(rs.getString("category"));
				newBean.setHomepage(rs.getInt("homepage"));
				newBean.setPuton(rs.getInt("puton"));
				System.out.println(newBean.getPicture());
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		System.out.println( "end findNewsById :");
		return newBean;
		
	}
//通过时间排序
public List<NewsBean> findAllByLbForPX(String lbString) {
	System.out.println("run findAllByLb " + lbString);
	List<NewsBean> list = new ArrayList<NewsBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	String sql = "select * from news where category = ? and puton=1 order by time desc";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		rs = statement.executeQuery();
		while (rs.next()) {
			NewsBean newBean = new NewsBean();
			newBean.setId(rs.getInt("id"));
			newBean.setTitle(rs.getString("title"));
			newBean.setContent(rs.getString("content"));
			newBean.setTime(rs.getDate("time"));
			newBean.setPicture(rs.getString("picture"));
			newBean.setAuthor(rs.getString("author"));
			newBean.setCategory(rs.getString("category"));
			newBean.setHomepage(rs.getInt("homepage"));
			list.add(newBean);
			System.out.println("findAllByLb  " + newBean);
		}
		
	} catch (Exception e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}

//查找上一条的ID
public NewsBean findPreNewsById(List<NewsBean> listNewsBean, int id) {
	NewsBean newBean = null;
	for(int i=0;i<listNewsBean.size();i++) {
		if(id == listNewsBean.get(i).getId()&&i!=0) {
			newBean = listNewsBean.get(i-1);
			return newBean;
		}
	}
	return newBean;
	 
	}


public NewsBean findNextNewsById(List<NewsBean> listNewsBean,int id) {
	NewsBean newBean = null;
	for(int i=0;i<listNewsBean.size();i++) {
		if(id == listNewsBean.get(i).getId()&&i+1<listNewsBean.size()) {
			newBean = listNewsBean.get(i+1);
			return newBean;
		}
	}
	return newBean;
	 
	 
	}

//通过id来查找上一条数据
public NewsBean findPreNewsById(int id) {
	System.out.println("run findNewsByid  nad ib");
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	NewsBean newBean = new NewsBean();
	String sql = "select * from news where id < ? and puton=1 order by id desc limit 1 ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setInt(1, id);
		System.out.println(sql);
		rs = statement.executeQuery();
		System.out.println(sql);
		
		if(rs.next()) {
			newBean.setId(rs.getInt("id"));
			newBean.setTitle(rs.getString("title"));
			newBean.setContent(rs.getString("content"));
			newBean.setTime(rs.getDate("time"));
			newBean.setAuthor(rs.getString("author"));
			newBean.setSource(rs.getString("source"));
			newBean.setPicture(rs.getString("picture"));
			newBean.setPuton(rs.getInt("puton"));
			
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}

	return newBean;
	
}
//通过id来查找下一条数据
public NewsBean findNextNewsById( int id) {
	
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	NewsBean newBean = new NewsBean();
	String sql = "select * from news where id > ? and homepage=1 and puton=1 order by id asc limit 1 ";
	try {
		
		statement = connection.prepareStatement(sql);
		statement.setInt(1, id);
		System.out.println(sql);
		rs = statement.executeQuery();
		System.out.println(sql);
		if(rs.next()) {
			newBean.setId(rs.getInt("id"));
			newBean.setTitle(rs.getString("title"));
			newBean.setContent(rs.getString("content"));
			newBean.setTime(rs.getDate("time"));
			newBean.setAuthor(rs.getString("author"));
			newBean.setSource(rs.getString("source"));
			newBean.setPicture(rs.getString("picture"));
			newBean.setPuton(rs.getInt("puton"));
			System.out.println(newBean.getPicture());
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}

	return newBean;
	
}
//前端类别查找
public List<NewsBean> findAllByLb(String lbString) {
	List<NewsBean> list = new ArrayList<NewsBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	String sql = "select * from news where category =? and puton=1 order by time desc";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		rs = statement.executeQuery();
		while(rs.next()) {
			NewsBean newBean = new NewsBean(); 
			newBean.setCategory(rs.getString("category"));
			newBean.setId(rs.getInt("id"));
			newBean.setTitle(rs.getString("title"));
		    newBean.setContent(rs.getString("content"));
			newBean.setAuthor(rs.getString("author"));
			newBean.setSource(rs.getString("source"));
			newBean.setTime(rs.getDate("time"));
			newBean.setPicture(rs.getString("picture"));
			System.out.println(newBean.getPicture());
			newBean.setPuton(rs.getInt("puton"));
			list.add(newBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}

//后端页面通过类别查找
public List<NewsBean> findbackByLb(String lbString) {
	List<NewsBean> list = new ArrayList<NewsBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	String sql = "select * from news where category =?";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		rs = statement.executeQuery();
		while(rs.next()) {
			NewsBean newBean = new NewsBean();
			newBean.setCategory(rs.getString("category"));
			newBean.setId(rs.getInt("id"));
			newBean.setTitle(rs.getString("title"));
		    newBean.setContent(rs.getString("content"));
			newBean.setAuthor(rs.getString("author"));
			newBean.setSource(rs.getString("source"));
			newBean.setTime(rs.getDate("time"));
			newBean.setPicture(rs.getString("picture"));
			System.out.println(newBean.getPicture());
			list.add(newBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}
//总数查询
public int findCountByLb(String lbString) {
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	int count = 0;
	String sql = "select count(*) totalCount from news where category =? and puton=1";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		rs = statement.executeQuery();
		if (rs.next()) {
			count=rs.getInt("totalCount");
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return count;
	
}


//分页
public List<NewsBean> findAllByLbForFY(String lbString,Page page) {
	List<NewsBean> list = new ArrayList<NewsBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	String sql = "select * from news where category =? and puton=1 order by time desc limit ?,? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		statement.setInt(2, page.getBeginIndex());
		statement.setInt(3, page.getEveryPage());
		rs = statement.executeQuery();
		while(rs.next()) {
			NewsBean newBean = new NewsBean();
			newBean.setCategory(rs.getString("category"));
			newBean.setId(rs.getInt("id"));
			newBean.setTitle(rs.getString("title"));
		    newBean.setContent(rs.getString("content"));
			newBean.setAuthor(rs.getString("author"));
			newBean.setSource(rs.getString("source"));
			newBean.setTime(rs.getDate("time"));
			newBean.setPicture(rs.getString("picture"));
			System.out.println(newBean.getPicture());
			list.add(newBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}
//后端查询总数
public int findbackCountByLb(String lbString) {
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	int count = 0;
	String sql = "select count(*) totalCount from news where category =? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		rs = statement.executeQuery();
		if (rs.next()) {
			count=rs.getInt("totalCount");
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return count;
	
}
//后端分页
public List<NewsBean> findbackByLbForFY(String lbString,Page page) {
	List<NewsBean> list = new ArrayList<NewsBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	String sql = "select * from news where category =? order by time desc limit ?,? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		statement.setInt(2, page.getBeginIndex());
		statement.setInt(3, page.getEveryPage());
		rs = statement.executeQuery();
		while(rs.next()) {
			NewsBean newBean = new NewsBean();
			newBean.setCategory(rs.getString("category"));
			newBean.setId(rs.getInt("id"));
			newBean.setPuton(rs.getInt("puton"));
			newBean.setHomepage(rs.getInt("homepage"));
			newBean.setTitle(rs.getString("title"));
		    newBean.setContent(rs.getString("content"));
			newBean.setAuthor(rs.getString("author"));
			newBean.setSource(rs.getString("source"));
			newBean.setTime(rs.getDate("time"));
			newBean.setPicture(rs.getString("picture"));
			newBean.setCategoryname(rs.getString("categoryname"));
			System.out.println(newBean.getPicture());
			list.add(newBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}

//后端模糊查询后总数
public int findselectCountByLb(String lbString,String tilString) {
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	int count = 0;
	String sql = "select count(*) totalCount from news where category =? and concat(id,title,content,category,author,source,time,puton) like ? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		statement.setString(2,"%" + tilString + "%");
		rs = statement.executeQuery();
		if (rs.next()) {
			count=rs.getInt("totalCount");
			System.out.print("totalCount :"+count);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return count;
	
}

		//模糊查询带上分页
		 public List<NewsBean> Proselect(String lbString,String tilString,Page page) {
		    List<NewsBean> list =new ArrayList<NewsBean>();
		    DBUtil dbUtil = new DBUtil();
		    connection = dbUtil.geConnection();
		    //String sql = "select * from news";
		    String sql ="select * from news where category =? and concat(id,title,content,category,author,source,time,puton) like ? order by time desc limit ?,?" ;
		    connection = dbUtil.geConnection();
		    try {
		     statement = connection.prepareStatement(sql);
		     statement.setString(1, lbString);
		     statement.setString(2,"%" + tilString + "%");
		     statement.setInt(3, page.getBeginIndex());
		     statement.setInt(4, page.getEveryPage());
		     rs = statement.executeQuery();
		     while(rs.next()) {
		    	   NewsBean newBean = new NewsBean();
				   newBean.setId(rs.getInt("id"));
				   newBean.setTitle(rs.getString("title"));
				   newBean.setContent(rs.getString("content"));
				   newBean.setCategory(rs.getString("category"));
				   newBean.setAuthor(rs.getString("author"));
				   newBean.setSource(rs.getString("source"));
				   newBean.setTime(rs.getDate("time"));
				   newBean.setPicture(rs.getString("picture"));
				   newBean.setPuton(rs.getInt("puton"));
				   newBean.setHomepage(rs.getInt("homepage"));
				   newBean.setCategoryname(rs.getString("categoryname"));
				   list.add(newBean);
		     }
		     
		    } catch (SQLException e) {
		     e.printStackTrace();
		    }finally {
		     if (rs != null) {
		      try {
		       rs.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     if (statement != null) {
		      try {
		       statement.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     if(connection != null) {
		      try {
		       connection.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     dbUtil.close();
		    }
		    return list;
		    
		   }
	
		
	
}
